<?php

$con = mysqli_connect("localhost","root","root");

if (!$con)
{
    die('连接失败: ' . mysqli_error($con));
}else{
    echo "连接成功<br>";
}

if (mysqli_select_db($con,"PHPProjects")){
    echo "连接数据库成功<br>";
}else{
    echo "连接数据库失败<br>";
}

//$result = mysqli_query($con,"SELECT * FROM comment");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '哈%'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '%哈%'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '_哈哈'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '嘻_哈_'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '[x]%'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment LIKE '[!x]%'");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE comment IN ('哈哈哈','嘻嘻哈哈')");
//$result = mysqli_query($con,"SELECT * FROM comment WHERE id BETWEEN 2 AND '5'");
//$result = mysqli_query($con,"SELECT * FROM comment  WHERE id NOT BETWEEN '2' AND 5");

//$sql = "SELECT user.id, comment.uname AS Uname, comment.comment AS Comment
//FROM user, comment
//WHERE user.idc = comment.id";

//$sql = "SELECT user.id,comment.uname,comment.comment FROM comment INNER JOIN user ON
//    user.idc = comment.id ORDER BY user.id";
//$sql = "SELECT user.id,comment.uname,comment.comment FROM comment INNER JOIN user ON
//    user.idc = comment.id ORDER BY user.id";
//$sql = "SELECT comment.uname,comment.comment,user.idc FROM comment LEFT JOIN user ON
//    comment.id = user.idc ORDER BY user.id";
//$sql = "SELECT comment.uname,comment.comment,user.idc FROM comment RIGHT JOIN user ON
//    comment.id = user.idc ORDER BY user.id";
//$sql = "SELECT comment.uname,comment.comment,user.idc FROM comment FULL JOIN user ON
//    comment.id = user.idc ORDER BY user.idc";
//$sql = "SELECT idc FROM comment UNION ALL SELECT idc FROM user";
//$sql = "SELECT uname,comment INTO comment_backup FROM comment WHERE id=1";
//$sql = "SELECT comment.uname,user.idc INTO comments FROM comment
//INNER JOIN user ON comment.id = user.idc";

//$sql = "CREATE TABLE Persons
//(
//    id int NOT NULL,
//    Lastname varchar(255) NOT NULL,
//    Address varchar(255),
//    City varchar(255),
//    CONSTRAINT uc_PersonsID UNIQUE (id,Lastname)
//)";
//$sql = "ALTER TABLE Persons ADD CONSTRAINT uc_PersonsId
//    UNIQUE (id,Lastname)";
//$sql = "ALTER TABLE Persons DROP INDEX uc_PersonsId";

//$sql = "CREATE TABLE Persons
//(
//    id int  NOT NULL,
//    City varchar(255) NOT NULL,
//    CONSTRAINT pk_PersonID PRIMARY KEY (id,City)
//)";
//$sql = "ALTER TABLE Persons ADD CONSTRAINT pk_PersonsID
//    PRIMARY KEY (id,City)";
//$sql = "ALTER TABLE Persons DROP PRIMARY KEY";

//FOREIGN KEY约束
//$sql = "CREATE TABLE Orders
//(
//    Id_O int NOT NULL,
//    OrderNo int NOT NULL,
//    Id_P int,
//    PRIMARY KEY (Id_O),
//    CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
//        REFERENCES Persons(Id_P)
//)";
//$sql = "ALTER TABLE Orders
//    ADD CONSTRAINT fk_PerOrders
//        FOREIGN KEY (Id_P)
//            REFERENCES Persons(Id_P)";
//$sql = "ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders";

//$sql = "CREATE TABLE Persons
//(
//    Id_P int NOT NULL,
//    LastName varchar(255) NOT NULL,
//    FirstName varchar(255),
//    Address varchar(255),
//    City varchar(255),
//    CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
//)";
//$sql = "ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')";

//$sql = "CREATE TABLE Orders
//(
//    Id_O int NOT NULL,
//    OrderNo int NOT NULL,
//    Id_P int,
//    City varchar(255) DEFAULT 'Sandnes'
//)";
//function getStr(): string
//{
//    return "2022-06-07";
//}
//$sql = "INSERT INTO Orders (Id_O,OderNo,Id_P) VALUES (2,3,2)";
//$sql = "ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'";
//$sql = "ALTER TABLE Persons ALTER City DROP DEFAULT";

//索引
//$sql = "CREATE INDEX Index_Pers ON Person (LastName)";
//$sql = "CREATE INDEX Index_Pers ON Person (LastName DESC)";
//$aql = "CREATE INDEX Index_Pers ON Person (LastName, FirstName)";
//$sql = "ALTER TABLE user ADD Birthday DATE";
//$sql = "ALTER TABLE user ALTER COLUMN Birthday year";
//$sql = "ALTER TABLE user DROP COLUMN Birthday";
//$sql = "select * from user where date = '2022-06-03'";
//$sql = "select *from user where date is null";
//$sql = "select *from user where date is not null";
//$sql = "SELECT id,clickname*(idc+IFNULL(udate,0)) FROM user";
//$sql = "SELECT id,clickname*(idc+COALESCE(udate,0)) FROM user";
//$sql = "SELECT id,clickname FROM user WHERE id>(SELECT AVG(id) FROM user)";
//$sql = "SELECT COUNT(clickname) AS Name FROM user WHERE idc='1'";
//$sql = "SELECT COUNT(*) AS Name FROM user";
//$sql = "SELECT COUNT(DISTINCT idc) AS Name FROM user";
//$sql = "SELECT FIRST(idc) AS uName FROM user";
//$sql = "SELECT LAST(idc) AS uName FROM user";
//$sql = "SELECT MAX(idc) AS uName FROM user";
//$sql = "SELECT MIN(clickname) AS uName FROM user";
//$sql = "SELECT SUM(idc) AS uName FROM user";
//$sql = "SELECT clickname,SUM(orderprice) FROM user GROUP BY clickname";
//$sql = "SELECT clickname,udate,SUM(orderprice) FROM user GROUP BY clickname,udate";
//$sql = "SELECT clickname,SUM(orderprice) FROM user GROUP BY clickname HAVING SUM(orderprice) >= 200";
//$sql = "SELECT clickname,SUM(orderprice) FROM user WHERE clickname = '从' OR clickname = '好'
//                                           GROUP BY clickname HAVING SUM(orderprice) > 300";
//$sql = "SELECT UCASE(clickname) AS udate FROM user";
//$sql = "SELECT LCASE(clickname) AS udate FROM user";
//$sql = "SELECT MID(clickname,2,4) AS udate FROM user";
//$sql = "SELECT LEN(clickname) AS udate FROM user";
//$sql = "SELECT clickname,ROUND(orderprice,0) AS udate FROM user";
//$sql = "SELECT clickname,udate,Now() AS idc FROM user";
$sql = "SELECT clickname,orderprice AS orderprice,FORMAT(Now(),'YY-mm-DD') AS udate FROM user";













if ($result = mysqli_query($con,$sql)){
    echo "成功";
}else{
    echo "失败";
}

echo "<table border='1'>
<tr>
<th>编号</th>
<th>名字</th>
<th>价格</th>
<th>评论</th>
<th>时间</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
    echo "<tr>";
    echo "<td>" . $row['SUM(orderprice)'] . "</td>";
    echo "<td>" . $row['clickname'] . "</td>";
    echo "<td>" . $row['orderprice'] . "</td>";
    echo "<td>" . $row['udate'] . "</td>";
    echo "<td>" . $row['idc'] . "</td>";
    echo "</tr>";
}
echo "</table>";

//echo "<table border='1'>
//<tr>
//<th>名字</th>
//<th>评论</th>
//<th>编号</th>
//</tr>";
//while($row = mysqli_fetch_array($result))
//{
//    echo "<tr>";
//    echo "<td>" . $row['uname'] . "</td>";
//    echo "<td>" . $row['comment'] . "</td>";
//    echo "<td>" . $row['idc'] . "</td>";
//    echo "</tr>";
//}
//echo "</table>";




mysqli_close($con);



